﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Frame.Global;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using System.Web.UI;
using System.Web;

namespace Frame.Global
{
    [System.ComponentModel.DesignerCategory("Code")]
    [Serializable]
    public class uDataSet : DataSet
    {
        private string QueryString = "";
        private string QueryStringOld = "";
        private string _MainTableName = string.Empty;
        private SqlParameter[] QueryParams;

        #region 公共属性
        /// <summary>
        /// 数据更新语句
        /// 用于更新数据使用，当未设置时即为初始的选择SQL（QueryStringOld）
        /// </summary>
        public string UpdateSql
        {
            get
            {
                if (string.IsNullOrEmpty(QueryString))
                {
                    return QueryStringOld;
                }
                else
                {
                    return QueryString;
                }

            }
            set { QueryString = value; }
        }

        public string TheMainTableName
        {
            get { return _MainTableName; }
        }
        /// <summary>
        /// 查询语句
        /// </summary>
        public string SelectSql
        {
            get { return QueryStringOld; }
            //set { QueryStringOld = value; }
        }

        public SqlParameter[] SqlParams
        {
            get { return QueryParams; }
            set { QueryParams = value; }
        }
        #endregion

        #region 构造函数
        /// <summary>
        /// 
        /// </summary>
        public uDataSet() { }
        private uDataSet(SerializationInfo info, StreamingContext context) : base(info, context) { }
        /// <summary>
        /// 构造uDataSet，得到一个包含数据的DataSet
        /// </summary>
        /// <param name="pStrQueryString">查询SQL</param>
        public uDataSet(string pStrQueryString)
        {
            QueryStringOld = pStrQueryString;
            this.Tables.Add(GlobalSqlHelper.ExecuteSQLToDataTable(pStrQueryString));
        }

        /// <summary>
        /// 构造uDataSet，得到一个包含数据的DataSet
        /// </summary>
        /// <param name="pStrQueryString"></param>
        /// <param name="parms"></param>
        public uDataSet(string pStrQueryString, params SqlParameter[] parms)
        {
            QueryStringOld = pStrQueryString;
            this.QueryParams = parms;
            this.Tables.Add(GlobalSqlHelper.ExecuteSQLToDataTable(pStrQueryString, parms));
        }

        /// <summary>
        /// 构造uDataSet，得到一个包含数据的DataSet
        /// </summary>
        /// <param name="pStrQueryString">查询SQL</param>
        /// <param name="pStrTableName">在DataSet中的表名</param>
        public uDataSet(string pStrQueryString, string pStrTableName)
        {
            QueryStringOld = pStrQueryString;
            _MainTableName = pStrTableName;
            this.Tables.Add(GlobalSqlHelper.ExecuteSQLToDataTable(pStrQueryString, pStrTableName));
            this.Tables[0].TableName = pStrTableName;
        }

        /// <summary>
        /// 构造uDataSet，得到一个包含数据的DataSet
        /// </summary>
        /// <param name="pStrQueryString"></param>
        /// <param name="pStrTableName"></param>
        /// <param name="parms"></param>
        public uDataSet(string pStrQueryString, string pStrTableName, params SqlParameter[] parms)
        {
            QueryStringOld = pStrQueryString;
            this.QueryParams = parms;
            _MainTableName = pStrTableName;
            this.Tables.Add(GlobalSqlHelper.ExecuteSQLToDataTable(pStrQueryString, pStrTableName, parms));
            this.Tables[0].TableName = pStrTableName;
        }
        /// <summary>
        /// 构造uDataSet，得到一个包含数据的DataSet
        /// 带有连接和事务的操作，慎用！
        /// 注意事务的Commit和Rollback
        /// 注意连接的关闭（close）
        /// </summary>
        /// <param name="myConnection"></param>
        /// <param name="myTransaction"></param>
        /// <param name="pStrQueryString"></param>
        /// <param name="pStrTableName"></param>
        /// <param name="parms"></param>
        public uDataSet(SqlConnection myConnection, SqlTransaction myTransaction, string pStrQueryString, string pStrTableName, params SqlParameter[] parms)
        {
            QueryStringOld = pStrQueryString;
            this.QueryParams = parms;
            _MainTableName = pStrTableName;
            this.Tables.Add(GlobalSqlHelper.ExecuteSQLToDataTable(myConnection, myTransaction, pStrQueryString, pStrTableName, parms));
            this.Tables[0].TableName = pStrTableName;
        }



        #endregion

        #region Retrieve 数据查询
        /// <summary>
        /// 查询主表数据
        /// </summary>
        /// <param name="pStrQueryString">传入的查询语句</param>
        /// <returns></returns>
        public int Retrieve(string pStrQueryString)
        {
            QueryStringOld = pStrQueryString;
            if (this.Tables.Count > 1)
            {
                this.Tables.Remove(_MainTableName);
            }
            else
            {
                this.Tables.Clear();
            }
            DataTable dt = GlobalSqlHelper.ExecuteSQLToDataTable(pStrQueryString);
            if (!string.IsNullOrEmpty(TheMainTableName))
            {
                dt.TableName = TheMainTableName;
                this.Tables.Add(dt);
                return this.Tables[TheMainTableName].Rows.Count;
            }
            else
            {
                this.Tables.Add(dt);
                return dt.Rows.Count;
            }
        }
        /// <summary>
        /// 查询主表数据
        /// </summary>
        /// <returns></returns>
        public int Retrieve()
        {
            if (this.Tables.Count > 1)
            {
                this.Tables.Remove(_MainTableName);
            }
            else
            {
                this.Tables.Clear();
            }
            DataTable dt = GlobalSqlHelper.ExecuteSQLToDataTable(SelectSql, QueryParams);
            if (!string.IsNullOrEmpty(TheMainTableName))
            {
                dt.TableName = TheMainTableName;
                this.Tables.Add(dt);
                return this.Tables[TheMainTableName].Rows.Count;
            }
            else
            {
                this.Tables.Add(dt);
                return dt.Rows.Count;
            }
        }
        /// <summary>
        /// 直接由表名获取所有数据
        /// </summary>
        /// <param name="TableName"></param>
        public void LoadDateFormTable(string TableName)
        {
            this.Tables.Clear();
            QueryParams = null;
            _MainTableName = TableName;
            QueryString = "select * from " + TableName;
            QueryStringOld = "select * from " + TableName;
            this.Retrieve();
        }
        #endregion

        #region 保存
        public int Update()
        {
            string mTableName = this.Tables[0].TableName;
            if (GlobalSqlHelper.UpdateByDataSet(UpdateSql, mTableName, this))
            {
                return 0;
            }
            else
            {
                return -1;
            }
        }

        public int Update(string pUpdateSql)
        {
            string mTableName = this.Tables[0].TableName;
            if (GlobalSqlHelper.UpdateByDataSet(pUpdateSql, mTableName, this))
            {
                return 0;
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        /// 单纯更新，使用事务
        /// </summary>
        /// <param name="pTr"></param>
        /// <returns></returns>
        public int Update(SqlTransaction pTr)
        {
            string mTableName = this.Tables[0].TableName;
            pTr = GlobalRunParms.PubdataConncetion.BeginTransaction();
            GlobalSqlHelper.UpdateByDataSet(UpdateSql, mTableName, this, GlobalRunParms.PubdataConncetion, pTr);
            return 0;
        }
        /// <summary>
        /// 根据DataSet中数据的变化更新数据库中的数据数据，使用特定的数据链接和事务
        /// </summary>
        /// <param name="sqlconnect"></param>
        /// <param name="pTr"></param>
        /// <returns></returns>
        public int Update(SqlConnection sqlconnect, SqlTransaction pTr)
        {
            string mTableName = this.Tables[0].TableName;
            if (GlobalSqlHelper.UpdateByDataSet(UpdateSql, mTableName, this, sqlconnect, pTr))
            {
                return 0;
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        /// 根据DataSet中数据的变化更新数据库中的数据数据，使用特定的数据链接和事务和对照查询语句
        /// </summary>
        /// <param name="pUpdateSql"></param>
        /// <param name="sqlconnect"></param>
        /// <param name="pTr"></param>
        /// <returns></returns>
        public int Update(string pUpdateSql, SqlConnection sqlconnect, SqlTransaction pTr)
        {
            string mTableName = this.Tables[0].TableName;
            if (GlobalSqlHelper.UpdateByDataSet(pUpdateSql, mTableName, this, sqlconnect, pTr))
            {
                return 0;
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        ///根据DataSet中数据的变化更新数据库中的数据数据，使用特定的数据链接和事务、特殊的对照查询语句和更新语句
        ///此方法一般很少用。
        /// </summary>
        /// <param name="pSqlSelect"></param>
        /// <param name="pSqlUpdate"></param>
        /// <param name="pSqlParamerter"></param>
        /// <param name="sqlconnect"></param>
        /// <param name="pTr"></param>
        /// <returns></returns>
        public int Update(string pSqlSelect, string pSqlUpdate, SqlParameter[] pSqlParamerter, SqlConnection sqlconnect, SqlTransaction pTr)
        {
            string mTableName = this.Tables[0].TableName;
            if (GlobalSqlHelper.UpdateByDataSet(pSqlSelect, mTableName, pSqlUpdate, pSqlParamerter, this, sqlconnect, pTr))
            {
                return 0;
            }
            else
            {
                return -1;
            }
        }
        /// <summary>
        /// 直接执行某语句，带数据链接和事务
        /// </summary>
        /// <param name="pSqlSelect"></param>
        /// <param name="sqlconnect"></param>
        /// <param name="pTr"></param>
        /// <returns></returns>
        public int ExcuteSQL(string pSqlCmd, SqlConnection sqlconnect, SqlTransaction pTr)
        {
            if (GlobalSqlHelper.ExecuteSQL(pSqlCmd, pTr))
            {
                return 0;
            }
            else
            {
                return -1;
            }
        }
        #endregion

        #region 导出Excel
        public void ExportToExcel(Page pPage, string FileName)
        {

            CreateExcel(pPage, "1", FileName);

        }



        public void ExportToXml(Page pPage, string FileName)
        {

            CreateExcel(pPage, "2", FileName);

        }


        private void CreateExcel(Page pPage, string typeid, string FileName)
        {
            HttpResponse resp;
            resp = pPage.Response; ;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            int i = 0;

            //定义表对象和行对像，同时用DataSet对其值进行初始化 
            DataTable dt = this.Tables[0];
            DataRow[] myRow = dt.Select("");
            // typeid=="1"时导出为EXCEL格式文档；typeid=="2"时导出为XML格式文档 
            if (typeid == "1")
            {
                //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                    }
                    else
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                    }
                }
                //向HTTP输出流中写入取得的数据信息 
                resp.Write(colHeaders);
                //逐行处理数据 
                foreach (DataRow row in myRow)
                {
                    //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                    for (i = 0; i < dt.Columns.Count; i++)
                    {

                        if (i == dt.Columns.Count - 1)
                        {
                            ls_item += row[i].ToString() + "\n";
                        }
                        else
                        {
                            ls_item += row[i].ToString() + "\t";
                        }
                    }
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                    resp.Write(ls_item);
                    ls_item = "";
                }
            }
            else
            {
                if (typeid == "2")
                {
                    //从DataSet中直接导出XML数据并且写到HTTP输出流中 
                    resp.Write(this.GetXml());
                }
            }
            //写缓冲区中的数据到HTTP头文档中 
            resp.End();
        }
        #endregion

    }
}
